package org.stvd.core.jdbc;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.stvd.core.web.SpringContextUtil;

public class DbUtil {
    private static JdbcTemplate jdbcTemplate = (JdbcTemplate) SpringContextUtil.getBean("jdbcTemplate");

    /**
     * 通过sql语句返回结果集
     */
    public static <T> List<T> find(String sql, Object[] parameters, Class<T> cl) {
        List<T> resultList = null;
        try {
            if (parameters != null && parameters.length > 0)
                resultList = jdbcTemplate.query(sql, parameters, new BeanPropertyRowMapper<T>(cl));
            else
                resultList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<T>(cl));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return resultList;
    }

    /**
     * 查询并返回一条记录
     */
    public static Map<String, Object> findForMap(String sql, Object... parameters) {
        try {
            return (Map<String, Object>) jdbcTemplate.queryForMap(sql, parameters);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    /**
     * 查询并返回集合，多条记录
     */
    public static List<?> findForList(String sql, Object[] parameters) {
        return jdbcTemplate.queryForList(sql, parameters);
    }

    /**
     * 获取记录数
     */
    public static int getCount(String sql, Object[] parameters) {
        return jdbcTemplate.queryForObject(sql, parameters, Integer.class);
    }

    /**
     * 添加，更新，删除的实现，返回1,0,-1
     * 
     */
    public static int execute(String sql, final Object[] parameters) {
        int num = 0;
        try {
            if (parameters == null || parameters.length == 0) {
                num = jdbcTemplate.update(sql);
            } else {
                num = jdbcTemplate.update(sql, new PreparedStatementSetter() {
                    @Override
                    public void setValues(PreparedStatement ps) throws SQLException {
                        for (int i = 0; i < parameters.length; i++)
                            ps.setObject(i + 1, parameters[i]);
                    }
                });
            }
        } catch (Exception e) {
            e.printStackTrace();
            num = -1;
        }
        return num;
    }

    public static boolean tableExist(String tableName) {
        Connection conn = null;
        ResultSet tabs = null;
        try {
            conn = jdbcTemplate.getDataSource().getConnection();
            DatabaseMetaData dbMetaData = conn.getMetaData();
            String[] types = { "TABLE" };
            tabs = dbMetaData.getTables(null, null, tableName, types);
            if (tabs.next()) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            try {
                if (tabs != null) {
                    tabs.close();
                }
            } catch (SQLException e) {
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
            }
        }
        return false;
    }

    public static String[] getTableNames(String tableName) {
        Connection conn = null;
        ResultSet tabs = null;
        List<String> list = new ArrayList<String>();
        try {
            conn = jdbcTemplate.getDataSource().getConnection();
            DatabaseMetaData dbMetaData = conn.getMetaData();
            String[] types = { "TABLE" };
            tabs = dbMetaData.getTables(null, null, tableName + "%", types);
            while (tabs.next()) {
                list.add(tabs.getString("TABLE_NAME"));

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            try {
                if (tabs != null) {
                    tabs.close();
                }
            } catch (SQLException e) {
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
            }
        }
        String[] tableNames = {};
        return list.toArray(tableNames);
    }
}